﻿#region Using
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using PhotoGallery.Core.Business;
using CY.Utility.DBUtility;
#endregion Using

namespace PhotoGallery.DALProviders.SqlServerProvider
{
    public class FolderDescProvider : PhotoGallery.Core.Providers.DALProvider.IFolderDescProvider
    {
        #region Sql
        private static readonly string SqlSelectFolderDesc = "SELECT [Id], [EventInfo],[StartDate],[EndDate],[KeyWord],[Photographer],[Leader],[OtherLeader],[Guest],[InnerLocation],[OuterLocation],[EventDesc] FROM [FolderDesc] WHERE  [Id]=@Id;";

        private static readonly string SqlInsertFolderDesc = "INSERT INTO [FolderDesc]([Id],[EventInfo],[StartDate],[EndDate],[KeyWord],[Photographer],[Leader],[OtherLeader],[Guest],[InnerLocation],[OuterLocation],[EventDesc]) values(@Id,@EventInfo,@StartDate,@EndDate,@KeyWord,@Photographer,@Leader,@OtherLeader,@Guest,@InnerLocation,@OuterLocation,@EventDesc);";

        private static readonly string SqlUpdateFolderDesc = "UPDATE [FolderDesc] SET [EventInfo] = @EventInfo,[StartDate] = @StartDate,[EndDate] = @EndDate,[KeyWord] = @KeyWord,[Photographer] = @Photographer,[Leader] = @Leader,[OtherLeader] = @OtherLeader,[Guest] = @Guest,[InnerLocation] = @InnerLocation,[OuterLocation] = @OuterLocation,[EventDesc] = @EventDesc WHERE  [Id]=@Id;";

        private static readonly string SqlDeleteFolderDesc = "DELETE [FolderDesc] WHERE  [Id]=@Id;";

        private static readonly string SqlGetAllFolderDesc = "SELECT [Id], [EventInfo],[StartDate],[EndDate],[KeyWord],[Photographer],[Leader],[OtherLeader],[Guest],[InnerLocation],[OuterLocation],[EventDesc] FROM [FolderDesc];";



        #endregion

        #region Procedures
        /*

-- Begin of FolderDesc
IF OBJECT_ID(N'[USP_FolderDesc_Delete_By_Id]') IS NOT NULL
	DROP PROCEDURE [USP_FolderDesc_Delete_By_Id]

IF OBJECT_ID(N'[USP_FolderDesc_Select_By_Id]') IS NOT NULL
	DROP PROCEDURE [USP_FolderDesc_Select_By_Id]

IF OBJECT_ID(N'[USP_FolderDesc_Insert]') IS NOT NULL
	DROP PROCEDURE [USP_FolderDesc_Insert]

IF OBJECT_ID(N'[USP_FolderDesc_Update_By_Id]') IS NOT NULL
	DROP PROCEDURE [USP_FolderDesc_Update_By_Id]

IF OBJECT_ID(N'[USP_FolderDesc_SelectAll]') IS NOT NULL
	DROP PROCEDURE [USP_FolderDesc_SelectAll]

GO

--region [USP_FolderDesc_Delete_By_Id]
CREATE PROCEDURE [USP_FolderDesc_Delete_By_Id]
	@Id	int
AS
SET NOCOUNT ON
DELETE [FolderDesc] WHERE  [Id]=@Id;
--endregion
GO

--region [USP_FolderDesc_Select_By_Id]
CREATE PROCEDURE [USP_FolderDesc_Select_By_Id]
	@Id	int
AS
SET NOCOUNT ON
SELECT [Id], [EventInfo],[StartDate],[EndDate],[KeyWord],[Photographer],[Leader],[OtherLeader],[Guest],[InnerLocation],[OuterLocation],[EventDesc] FROM [FolderDesc] WHERE  [Id]=@Id;
--endregion
GO

--region [USP_FolderDesc_Insert]
CREATE PROCEDURE [USP_FolderDesc_Insert]
	@Id	int,
	@EventInfo	int,
	@StartDate	datetime,
	@EndDate	datetime,
	@KeyWord	nvarchar(50),
	@Photographer	nvarchar(30),
	@Leader	nvarchar(200),
	@OtherLeader	nvarchar(200),
	@Guest	nvarchar(200),
	@InnerLocation	nvarchar(500),
	@OuterLocation	nvarchar(500),
	@EventDesc	nvarchar(100)
AS
SET NOCOUNT ON
INSERT INTO [FolderDesc]([Id],[EventInfo],[StartDate],[EndDate],[KeyWord],[Photographer],[Leader],[OtherLeader],[Guest],[InnerLocation],[OuterLocation],[EventDesc]) values(@Id,@EventInfo,@StartDate,@EndDate,@KeyWord,@Photographer,@Leader,@OtherLeader,@Guest,@InnerLocation,@OuterLocation,@EventDesc);
--endregion
GO

--region [USP_FolderDesc_Update_By_Id]
CREATE PROCEDURE [USP_FolderDesc_Update_By_Id]
	@EventInfo	int,
	@StartDate	datetime,
	@EndDate	datetime,
	@KeyWord	nvarchar(50),
	@Photographer	nvarchar(30),
	@Leader	nvarchar(200),
	@OtherLeader	nvarchar(200),
	@Guest	nvarchar(200),
	@InnerLocation	nvarchar(500),
	@OuterLocation	nvarchar(500),
	@EventDesc	nvarchar(100),
	@Id	int
AS
SET NOCOUNT ON
UPDATE [FolderDesc] SET [EventInfo] = @EventInfo,[StartDate] = @StartDate,[EndDate] = @EndDate,[KeyWord] = @KeyWord,[Photographer] = @Photographer,[Leader] = @Leader,[OtherLeader] = @OtherLeader,[Guest] = @Guest,[InnerLocation] = @InnerLocation,[OuterLocation] = @OuterLocation,[EventDesc] = @EventDesc WHERE  [Id]=@Id;
--endregion
GO

--region [USP_FolderDesc_SelectAll]
CREATE PROCEDURE [USP_FolderDesc_SelectAll]
AS
SET NOCOUNT ON
SELECT [Id], [EventInfo],[StartDate],[EndDate],[KeyWord],[Photographer],[Leader],[OtherLeader],[Guest],[InnerLocation],[OuterLocation],[EventDesc] FROM [FolderDesc];
--endregion
GO


-- End of FolderDesc
*/
        #endregion

        #region IFolderDescProvider Members
        public Core.Business.FolderDesc Select(int id)
        {
            SqlServerUtility sql = new SqlServerUtility();

            sql.AddParameter("@Id", SqlDbType.Int, id);
            SqlDataReader reader = sql.ExecuteSqlReader(SqlSelectFolderDesc);

            if (reader != null && !reader.IsClosed && reader.Read())
            {
                Core.Business.FolderDesc folderDesc = new Core.Business.FolderDesc();

                if (!reader.IsDBNull(0)) folderDesc.Id = reader.GetInt32(0);
                if (!reader.IsDBNull(1)) folderDesc.EventInfo = reader.GetInt32(1);
                if (!reader.IsDBNull(2)) folderDesc.StartDate = reader.GetDateTime(2);
                if (!reader.IsDBNull(3)) folderDesc.EndDate = reader.GetDateTime(3);
                if (!reader.IsDBNull(4)) folderDesc.KeyWord = reader.GetString(4);
                if (!reader.IsDBNull(5)) folderDesc.Photographer = reader.GetString(5);
                if (!reader.IsDBNull(6)) folderDesc.Leader = reader.GetString(6);
                if (!reader.IsDBNull(7)) folderDesc.OtherLeader = reader.GetString(7);
                if (!reader.IsDBNull(8)) folderDesc.Guest = reader.GetString(8);
                if (!reader.IsDBNull(9)) folderDesc.InnerLocation = reader.GetString(9);
                if (!reader.IsDBNull(10)) folderDesc.OuterLocation = reader.GetString(10);
                if (!reader.IsDBNull(11)) folderDesc.EventDesc = reader.GetString(11);

                reader.Close();

                EventInfoProvider provider = new EventInfoProvider();

                folderDesc.EventInfoEntity = provider.Select(folderDesc.EventInfo);

                return folderDesc;
            }
            else
            {
                if (reader != null && !reader.IsClosed)
                    reader.Close();

                return null;
            }
        }

        public void Insert(Core.Business.FolderDesc folderDesc)
        {
            SqlServerUtility sql = new SqlServerUtility();

            sql.AddParameter("@EventInfo", SqlDbType.Int, folderDesc.EventInfo > 0 ? (object)folderDesc.EventInfo : (object)DBNull.Value);
            sql.AddParameter("@StartDate", SqlDbType.DateTime, folderDesc.StartDate);
            sql.AddParameter("@EndDate", SqlDbType.DateTime, folderDesc.EndDate);
            sql.AddParameter("@KeyWord", SqlDbType.NVarChar, folderDesc.KeyWord);
            sql.AddParameter("@Photographer", SqlDbType.NVarChar, folderDesc.Photographer == null ? string.Empty : folderDesc.Photographer);
            sql.AddParameter("@Leader", SqlDbType.NVarChar, folderDesc.Leader == null ? string.Empty : folderDesc.Leader);
            sql.AddParameter("@OtherLeader", SqlDbType.NVarChar, folderDesc.OtherLeader == null ? string.Empty : folderDesc.OtherLeader);
            sql.AddParameter("@Guest", SqlDbType.NVarChar, folderDesc.Guest == null ? string.Empty : folderDesc.Guest);
            sql.AddParameter("@InnerLocation", SqlDbType.NVarChar, folderDesc.InnerLocation == null ? string.Empty : folderDesc.InnerLocation);
            sql.AddParameter("@OuterLocation", SqlDbType.NVarChar, folderDesc.OuterLocation == null ? string.Empty : folderDesc.OuterLocation);
            sql.AddParameter("@EventDesc", SqlDbType.NVarChar, folderDesc.EventDesc == null ? string.Empty : folderDesc.EventDesc);
            sql.AddParameter("@Id", SqlDbType.Int, folderDesc.Id);
            sql.ExecuteSql(SqlInsertFolderDesc);
        }

        public void Update(Core.Business.FolderDesc folderDesc)
        {
            SqlServerUtility sql = new SqlServerUtility();

            sql.AddParameter("@EventInfo", SqlDbType.Int, folderDesc.EventInfo > 0 ? (object)folderDesc.EventInfo : (object)DBNull.Value);
            sql.AddParameter("@StartDate", SqlDbType.DateTime, folderDesc.StartDate);
            sql.AddParameter("@EndDate", SqlDbType.DateTime, folderDesc.EndDate);
            sql.AddParameter("@KeyWord", SqlDbType.NVarChar, folderDesc.KeyWord);
            sql.AddParameter("@Photographer", SqlDbType.NVarChar, folderDesc.Photographer);
            sql.AddParameter("@Leader", SqlDbType.NVarChar, folderDesc.Leader);
            sql.AddParameter("@OtherLeader", SqlDbType.NVarChar, folderDesc.OtherLeader);
            sql.AddParameter("@Guest", SqlDbType.NVarChar, folderDesc.Guest);
            sql.AddParameter("@InnerLocation", SqlDbType.NVarChar, folderDesc.InnerLocation);
            sql.AddParameter("@OuterLocation", SqlDbType.NVarChar, folderDesc.OuterLocation);
            sql.AddParameter("@EventDesc", SqlDbType.NVarChar, folderDesc.EventDesc);
            sql.AddParameter("@Id", SqlDbType.Int, folderDesc.Id);
            sql.ExecuteSql(SqlUpdateFolderDesc);
        }

        public void Delete(Core.Business.FolderDesc folderDesc)
        {
            SqlServerUtility sql = new SqlServerUtility();

            sql.AddParameter("@Id", SqlDbType.Int, folderDesc.Id);
            sql.ExecuteSql(SqlDeleteFolderDesc);
        }

        public IList<Core.Business.FolderDesc> GetAllFolderDesc()
        {
            IList<Core.Business.FolderDesc> folderDesclist = new List<Core.Business.FolderDesc>();
            SqlServerUtility sql = new SqlServerUtility();

            SqlDataReader reader = sql.ExecuteSqlReader(SqlGetAllFolderDesc);

            if (reader != null)
            {
                while (reader.Read())
                {
                    Core.Business.FolderDesc folderDesc = new Core.Business.FolderDesc();

                    if (!reader.IsDBNull(0)) folderDesc.Id = reader.GetInt32(0);
                    if (!reader.IsDBNull(1)) folderDesc.EventInfo = reader.GetInt32(1);
                    if (!reader.IsDBNull(2)) folderDesc.StartDate = reader.GetDateTime(2);
                    if (!reader.IsDBNull(3)) folderDesc.EndDate = reader.GetDateTime(3);
                    if (!reader.IsDBNull(4)) folderDesc.KeyWord = reader.GetString(4);
                    if (!reader.IsDBNull(5)) folderDesc.Photographer = reader.GetString(5);
                    if (!reader.IsDBNull(6)) folderDesc.Leader = reader.GetString(6);
                    if (!reader.IsDBNull(7)) folderDesc.OtherLeader = reader.GetString(7);
                    if (!reader.IsDBNull(8)) folderDesc.Guest = reader.GetString(8);
                    if (!reader.IsDBNull(9)) folderDesc.InnerLocation = reader.GetString(9);
                    if (!reader.IsDBNull(10)) folderDesc.OuterLocation = reader.GetString(10);
                    if (!reader.IsDBNull(11)) folderDesc.EventDesc = reader.GetString(11);

                    folderDesc.MarkOld();
                    folderDesclist.Add(folderDesc);
                }
                reader.Close();
            }
            return folderDesclist;
        }
        #endregion

        #region 查找

        public FolderDesc Find(int folderid)
        {
            throw new NotImplementedException();
        }
        #endregion

    }
}
